﻿using Dapper;
using ExcelConfig;
using MySqlX.XDevAPI;
using net_path.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static System.Net.Mime.MediaTypeNames;
using System.Xml.Linq;
using net_path.data.Model;

namespace net_path.data.Database
{
    public class NetPathDB : IDisposable
    {
        internal IRepo repo;
        public NetPathDB()
        {
            var connectstring = AppConfig.Text("net-path-db");
            repo = new MysqlRepo(connectstring);
        }
        // 生成用户
        public int MakeUser(string user, string password, bool cansend)
        {
            var lines = 0;
            repo.Open();

            var net_user = new NetUser();
            net_user.CanSend = cansend;
            net_user.Deleted = false;
            net_user.Password = password;
            net_user.CreateTime = DateTime.UtcNow;
            net_user.UpdateTime = DateTime.UtcNow;
            net_user.Name = user;
            net_user.UserId = Guid.NewGuid();
            var id = repo.Connection.QueryFirstOrDefault<Guid>("select UserId from net_user where Name=@Name;", net_user);
            var update_mode = false;
            if (id != default(Guid))
            {
                update_mode = true;
                net_user.UserId = id;
            }
            var update_sender_mode = false;
            var net_sender = new NetSender();
            net_sender.SenderId = Guid.NewGuid();
            net_sender.UserId = net_user.UserId;
            net_sender.Name = user;
            if (net_user.CanSend)
            {
                var id_sender = repo.Connection.QueryFirstOrDefault<Guid>("select SenderId from net_sender where UserId=@UserId;", net_sender);
                if (id_sender != default(Guid))
                {
                    update_sender_mode = true;
                    net_sender.SenderId = id_sender;
                }
            }

            using (var transaction = repo.Connection.BeginTransaction())
            {
                try
                {
                    if (update_mode) lines +=repo.Connection.Execute("update net_user set Name=@Name, Password=@Password, CanSend=@CanSend, Deleted=@Deleted, CreateTime=@CreateTime, UpdateTime=@UpdateTime, Deleted=@Deleted where UserId=@UserId;", net_user, transaction);
                    else lines += repo.Connection.Execute("insert into net_user (UserId, Name, Password, CanSend, Deleted, CreateTime, UpdateTime, DeleteTime) values (@UserId, @Name, @Password, @CanSend, @Deleted, @CreateTime, @UpdateTime, @DeleteTime);", net_user, transaction);
                    if (update_sender_mode) lines += repo.Connection.Execute("update net_sender set Name=@Name where SenderId=@SenderId", net_sender, transaction);
                    else lines += repo.Connection.Execute("insert into net_sender (SenderId, UserId, Name) values (@SenderId, @UserId, @Name);", net_sender, transaction);
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    // 发生异常时回滚事务
                    transaction.Rollback();
                    // 处理异常
                    Console.WriteLine(ex.Message);
                }
            }
            return lines;
        }
        // 生成发送服务
        public int MakeServe(string user, string name, string desc, string ip, int port, string password)
        {
            var lines = 0;
            repo.Open();

            var net_serve = new NetServe();
            net_serve.ServeId = Guid.NewGuid();
            net_serve.Name = name;
            net_serve.Description = desc;
            net_serve.IP = ip;
            net_serve.Port = port;
            net_serve.Password = password;
            net_serve.Deleted = false;
            var id_sender = repo.Connection.QueryFirstOrDefault<Guid>("select SenderId from net_sender where Name=@Name;", net_serve);
            if (id_sender != default(Guid)) return lines;
            net_serve.SenderId = id_sender;
            var id_serve = repo.Connection.QueryFirstOrDefault<Guid>("select ServeId from net_serve where SenderId=@SenderId and Name=@Name;", net_serve);
            var update_mode = false;
            if (id_serve != default(Guid))
            {
                update_mode = true;
                net_serve.ServeId = id_serve;
            }

            using (var transaction = repo.Connection.BeginTransaction())
            {
                try
                {
                    if (update_mode) lines+= repo.Connection.Execute("update net_serve set Name=@Name, Description=@Description, IP=@IP, Port=@Port, Password=@Password, Deleted=@Deleted  where ServeId=@ServeId;", net_serve, transaction);
                    else lines += repo.Connection.Execute("insert into net_serve (ServeId, SenderId, Name, Description, IP, Port, Password, Deleted) values (@ServeId, @SenderId, @Name, @Description, @IP, @Port, @Password, @Deleted);", net_serve, transaction);
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    // 发生异常时回滚事务
                    transaction.Rollback();
                    // 处理异常
                    Console.WriteLine(ex.Message);
                }
            }
            return lines;
        }
        // 删除发送服务
        public int DeleteServe(Guid id)
        {
            var lines = 0;
            repo.Open();

            var need_delete = repo.Connection.QueryFirstOrDefault<bool>("select true from net_serve where ServeId=@ServeId and Deleted=0;", new { ServeId = id });
            if (need_delete)
            {
                using (var transaction = repo.Connection.BeginTransaction())
                {
                    try
                    {
                        lines += repo.Connection.Execute("update net_serve set Deleted=1 where ServeId=@ServeId;", new { ServeId = id }, transaction);
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        // 发生异常时回滚事务
                        transaction.Rollback();
                        // 处理异常
                        Console.WriteLine(ex.Message);
                    }
                }

            }
            return lines;
        }
        // 生成 path
        public int MakePath(Guid serveId, string name, string desc, string password, string srcUri, int srcPort, int outPort)
        {
            var lines = 0;
            repo.Open();

            var net_path = new NetPath();
            net_path.PathId = Guid.NewGuid();
            net_path.ServeId = serveId;
            net_path.Name = name;
            net_path.Description = desc;
            net_path.Password = password;
            net_path.SrcUri = srcUri;
            net_path.SrcPort = srcPort;
            net_path.OutPort = outPort;
            var id_path = repo.Connection.QueryFirstOrDefault<Guid>("select PathId from net_path where ServeId=@ServeId and Name=@Name;", net_path);
            var update_mode = false;
            if (id_path != default(Guid))
            {
                update_mode = true;
                net_path.PathId= id_path;
            }

            using (var transaction = repo.Connection.BeginTransaction())
            {
                try
                {
                    if (update_mode) lines += repo.Connection.Execute("update net_path set Description=@Description, Password=@Password, SrcUri=@SrcUri, SrcPort=@SrcPort, OutPort=@OutPort where PathId=@PathId;", net_path, transaction);
                    else lines += repo.Connection.Execute("insert into net_path (PathId, ServeId, Name, Description, Password, SrcUri, SrcPort, OutPort) values (@PathId, @ServeId, @Name, @Description, @Password, @SrcUri, @SrcPort, @OutPort);", net_path, transaction);
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    // 发生异常时回滚事务
                    transaction.Rollback();
                    // 处理异常
                    Console.WriteLine(ex.Message);
                }
            }
            return lines;
        }
        // 建立联系 user <-> path
        public int MakeUserMap(Guid userId, Guid pathId)
        {
            var lines = 0;
            repo.Open();
            var net_usermap = new NetUserMap();
            net_usermap.MapId = Guid.NewGuid();
            net_usermap.UserId = userId;
            net_usermap.PathId = pathId;
            net_usermap.Deleted = false;
            var id_usermap = repo.Connection.QueryFirstOrDefault<Guid>("select MapId from new_usermap where UserId=@UserId and PathId=@PathId;", net_usermap);
            var update_mode = false;
            if (id_usermap != default(Guid))
            {
                update_mode = true;
                net_usermap.MapId = id_usermap;
            }

            using (var transaction = repo.Connection.BeginTransaction())
            {
                try
                {
                    if (update_mode) lines += repo.Connection.Execute("update net_usermap set Deleted=0 where MapId=@MapId;", net_usermap, transaction);
                    else lines += repo.Connection.Execute("insert into net_usermap (MapId, UserId, PathId, Deleted) values (@MapId, @UserId, @PathId, @Deleted);", net_usermap, transaction);
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    // 发生异常时回滚事务
                    transaction.Rollback();
                    // 处理异常
                    Console.WriteLine(ex.Message);
                }
            }
            return lines;
        }
        public void Dispose()
        {
            repo.Dispose();
        }
    }
}
